from typing import Any, Dict, Optional, Union, List
from datetime import datetime

from sqlalchemy.orm import Session

from app.crud.base import CRUDBase
from app.models.shipment import ShipmentMonitor
from app.schemas.shipment import ShipmentCreate, ShipmentUpdate


class CRUDShipment(CRUDBase[ShipmentMonitor, ShipmentCreate, ShipmentUpdate]):
    """
    货物追踪CRUD操作
    """
    def get_by_number(self, db: Session, *, number_no: str) -> Optional[ShipmentMonitor]:
        """
        根据单号获取货物追踪记录
        """
        return db.query(ShipmentMonitor).filter(ShipmentMonitor.number_no == number_no).first()
    
    def get_by_mac_id(self, db: Session, *, mac_id: str) -> List[ShipmentMonitor]:
        """
        根据设备号获取货物追踪记录
        """
        return db.query(ShipmentMonitor).filter(ShipmentMonitor.mac_id == mac_id).all()

    def get_by_status(self, db: Session, *, status: int, skip: int = 0, limit: int = 100) -> List[ShipmentMonitor]:
        """
        根据状态获取货物追踪记录
        """
        return db.query(ShipmentMonitor).filter(ShipmentMonitor.status == status).offset(skip).limit(limit).all()

    def get_active_shipments(self, db: Session) -> List[ShipmentMonitor]:
        """
        获取所有活跃的货物（未开始和进行中）
        """
        return db.query(ShipmentMonitor).filter(ShipmentMonitor.status.in_([0, 10])).all()
    
    def _filter_query(
        self,
        db: Session,
        *,
        number_no: Optional[str] = None,
        pickup_warehouse: Optional[str] = None,
        delivery_warehouse: Optional[str] = None,
        mac_id: Optional[str] = None,
        status: Optional[int] = None
    ):
        """
        构建带筛选条件的SQLAlchemy查询对象
        """
        query = db.query(ShipmentMonitor)
        if number_no:
            query = query.filter(ShipmentMonitor.number_no.like(f"%{number_no}%"))
        if pickup_warehouse:
            query = query.filter(ShipmentMonitor.pickup_warehouse.like(f"%{pickup_warehouse}%"))
        if delivery_warehouse:
            query = query.filter(ShipmentMonitor.delivery_warehouse.like(f"%{delivery_warehouse}%"))
        if mac_id:
            query = query.filter(ShipmentMonitor.mac_id == mac_id)
        if status is not None:
            query = query.filter(ShipmentMonitor.status == status)
        return query

    def search(
        self, 
        db: Session, 
        *, 
        number_no: Optional[str] = None,
        pickup_warehouse: Optional[str] = None,
        delivery_warehouse: Optional[str] = None,
        mac_id: Optional[str] = None,
        status: Optional[int] = None,
        skip: int = 0, 
        limit: int = 100
    ) -> List[ShipmentMonitor]:
        """
        搜索货物追踪记录
        """
        query = self._filter_query(
            db,
            number_no=number_no,
            pickup_warehouse=pickup_warehouse,
            delivery_warehouse=delivery_warehouse,
            mac_id=mac_id,
            status=status
        )
        return query.offset(skip).limit(limit).all()

    def count(
        self,
        db: Session,
        *,
        number_no: Optional[str] = None,
        pickup_warehouse: Optional[str] = None,
        delivery_warehouse: Optional[str] = None,
        mac_id: Optional[str] = None,
        status: Optional[int] = None
    ) -> int:
        """
        统计货物追踪记录总数，支持筛选
        """
        query = self._filter_query(
            db,
            number_no=number_no,
            pickup_warehouse=pickup_warehouse,
            delivery_warehouse=delivery_warehouse,
            mac_id=mac_id,
            status=status
        )
        return query.count()

    def update_route_data(self, db: Session, *, id: int, route_data: Dict[str, Any], updated_by: str = None) -> ShipmentMonitor:
        """
        更新货物路线数据
        """
        db_obj = self.get(db=db, id=id)
        if not db_obj:
            raise ValueError(f"找不到ID为 {id} 的货物追踪记录")
        
        db_obj.route_data = route_data
        db_obj.version += 1
        
        if updated_by:
            db_obj.update_by = updated_by
        
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

    def update_status_and_time(
        self, 
        db: Session, 
        *, 
        id: int, 
        status: int, 
        start_time: Optional[datetime] = None,
        end_time: Optional[datetime] = None,
        execution_info: Optional[str] = None,
        updated_by: str = None
    ) -> ShipmentMonitor:
        """
        更新货物状态和时间信息
        """
        db_obj = self.get(db=db, id=id)
        if not db_obj:
            raise ValueError(f"找不到ID为 {id} 的货物追踪记录")
        
        db_obj.status = status
        if start_time is not None:
            db_obj.start_time = start_time
        if end_time is not None:
            db_obj.end_time = end_time
        if execution_info is not None:
            db_obj.execution_info = execution_info
        
        db_obj.version += 1
        if updated_by:
            db_obj.update_by = updated_by
        
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

    def update_coordinates(
        self, 
        db: Session, 
        *, 
        id: int,
        pickup_coordinates: Optional[str] = None,
        delivery_coordinates: Optional[str] = None,
        updated_by: str = None
    ) -> ShipmentMonitor:
        """
        更新仓库坐标信息
        """
        db_obj = self.get(db=db, id=id)
        if not db_obj:
            raise ValueError(f"找不到ID为 {id} 的货物追踪记录")
        
        if pickup_coordinates is not None:
            db_obj.pickup_warehouse_coordinates = pickup_coordinates
        if delivery_coordinates is not None:
            db_obj.delivery_warehouse_coordinates = delivery_coordinates
        
        db_obj.version += 1
        if updated_by:
            db_obj.update_by = updated_by
        
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj


# 创建实例以便导入
shipment = CRUDShipment(ShipmentMonitor)